package com.apache.funds.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.apache.api.vo.ParamsVo;
import com.apache.database.constant.Validator;
import com.apache.database.db.IDao;
import com.apache.database.model.MethodParam;
import com.apache.database.model.Page;
import com.apache.exception.BusinessException;
import com.apache.funds.entity.SvmCaptionBalance;
import com.apache.funds.manager.SvmCaptionBalanceManager;
import com.apache.funds.util.Constants;
import com.apache.funds.util.FundsTemplate;
import com.apache.tools.DateUtils;
import com.apache.tools.DateUtils.FmtStr;

/**
 * 本类主要涉及业务逻辑<br>
 * 1.期初+发生的查询汇总 （execute 方法）<br>
 * 2.期初+发生的结果存储 （saveInfo 方法）<br>
 * @author Administrator
 *
 */
public class SvmCaptionBalanceManagerImpl implements SvmCaptionBalanceManager {
	private Logger log = LoggerFactory.getLogger(getClass());

	protected IDao svmCaptionBalanceDao;
	protected final String entityName = "com.apache.funds.entity.SvmCaptionBalance";

	public void setSvmCaptionBalanceDao(IDao svmCaptionBalanceDao) {
		this.svmCaptionBalanceDao = svmCaptionBalanceDao;
	}

	public String saveInfo(ParamsVo<SvmCaptionBalance> vo) throws BusinessException {
		SvmCaptionBalance sptsCaptionBalance = vo.getObj();
		if (Validator.isEmpty(sptsCaptionBalance) && !Validator.isEmpty(vo.getObjs())) {
			String mKey = vo.getMethodKey();
			boolean delFlag = true;
			//计算日终时，先删除记录,再添加（不再记录以往的日终信息，只保留最新信息）
			if ("OpenAndOccurBalance".equals(mKey)) {
				MethodParam delParam = new MethodParam("AllInfo", "", "", entityName);
				delParam.setDelete(true);
				delFlag = svmCaptionBalanceDao.delete(delParam);
			}
			//再添加
			if (delFlag) {
				MethodParam param = new MethodParam("Bath", "", "", entityName);
				param.setVaule(vo.getObjs());
				if (svmCaptionBalanceDao.insertBath(param)) {
					return "true";
				}
			}
		} else {
			//定义对象缓存KEY,如果不需要缓存对象请不要对变量赋值，如果要缓存对象建议使用infoId
			String cacheKey = "";
			//String cacheKey="sptsCaptionBalance_"+infoId;
			MethodParam param = new MethodParam("SvmCaptionBalance", cacheKey, "", entityName);
			param.setVaule(sptsCaptionBalance);
			if (svmCaptionBalanceDao.insert(param)) {
				return "";
			}
		}
		return "";
	}

	public boolean editInfo(ParamsVo<SvmCaptionBalance> vo) throws BusinessException {
		SvmCaptionBalance sptsCaptionBalance = vo.getObj();
		if (Validator.isNotNull(sptsCaptionBalance.getCaptionId())) {
			String cacheKey = "";
			//String cacheKey="sptsCaptionBalance_"+sptsCaptionBalance.getCaptionId();
			MethodParam param = new MethodParam("SvmCaptionBalance", cacheKey, "", entityName);
			param.setVaule(sptsCaptionBalance);
			return svmCaptionBalanceDao.edit(param);
		}
		return false;
	}

	public boolean deleteInfo(ParamsVo<SvmCaptionBalance> vo) throws BusinessException {
		String infoId = vo.getInfoId();
		if (Validator.isNull(infoId)) {
			return false;
		}
		String cacheKey = "";
		//String cacheKey="sptsCaptionBalance_"+infoId;
		String mark = Validator.getDefaultStr(String.valueOf(vo.getParams("isDelete")), "true");
		MethodParam param = new MethodParam("ById", cacheKey, "", entityName);
		param.setInfoId(infoId);
		SvmCaptionBalance info = (SvmCaptionBalance) svmCaptionBalanceDao.selectById(param);
		if (Validator.isEmpty(info)) {
			return false;
		}
		param.setVaule(info);//此处需要先将状态值赋值为删除状态
		if ("false".equals(mark)) {//逻辑删除
			param.setKey("SvmCaptionBalance");
			return svmCaptionBalanceDao.edit(param);
		} else {
			param.setParams("captionId", infoId);
			param.setDelete(true);
			return svmCaptionBalanceDao.delete(param);
		}
	}

	public Object getInfoById(ParamsVo<SvmCaptionBalance> vo) {
		String infoId = vo.getInfoId();
		String cacheKey = "";
		//String cacheKey="sptsCaptionBalance_"+infoId;
		if (Validator.isNull(infoId)) {
			return null;
		}
		MethodParam param = new MethodParam("ById", cacheKey, "", entityName);
		param.setInfoId(infoId);
		return svmCaptionBalanceDao.selectById(param);
	}

	public Object execute(ParamsVo<SvmCaptionBalance> vo) {
		try {
			//			return this.selectUserFunds(vo);
			return this.selectUserFunds_new(vo);
		} catch (Exception e) {
			return null;
		}
	}

	// 查询所有人或指定人的所有资金情况(方法作废)
	private Object selectUserFunds(ParamsVo<SvmCaptionBalance> vo) throws Exception {
		vo.setMethodKey("UserFunds");
		if (Validator.isEmpty(vo.getParams("dateTrade")) || Validator.isEmpty(vo.getParams("dateFlag"))) {
			Map<String, Object> mp = new HashMap<String, Object>();
			mp.put("tradeDate", DateUtils.getDate());
			long time = DateUtils.getDate().getTime();
			String dateTime = DateUtils.longToStr(time, FmtStr.yyyyMMdd);
			long dateTrade = DateUtils.toLong(DateUtils.parse(dateTime));
			vo.setParams("dateTrade", dateTrade);
			Date d = DateUtils.fromLong(dateTrade);
			Date yd = DateUtils.beforeNDays(d, 1);
			long dateTradeYesterday = DateUtils.toLong(yd);
			vo.setParams("dateTradeYesterday", dateTradeYesterday);
		} else {
			String dateTrade = vo.getParams("dateTrade").toString();
			String dateTradeYesterday = vo.getParams("dateTradeYesterday").toString();
			vo.setParams("dateTrade", dateTrade);
			vo.setParams("dateTradeYesterday", dateTradeYesterday);
		}

		String lastStr = DateUtils.longToStr(Long.valueOf(String.valueOf(vo.getParams("dateTradeYesterday"))),
				FmtStr.yyyyMMdd);
		String nowStr = DateUtils.longToStr(Long.valueOf(String.valueOf(vo.getParams("dateTrade"))), FmtStr.yyyyMMdd);

		log.info("fund资金余额查询：正在统计[" + vo.getParams("userId") + "]前日[" + lastStr + " | "
				+ vo.getParams("dateTradeYesterday") + "]的结存余额  + 当日[" + nowStr + " | " + vo.getParams("dateTrade")
				+ "]的发生值");
		MethodParam param = setMethodParams(vo, 2);
		this.buildSql(param);
		List list = svmCaptionBalanceDao.select(param);
		//		log.info("fund资金余额查询：结果 -> "+list.size());
		return list;
	}

	//查询所有人或指定人的所有资金情况(最近一次的日终 + 以后的发生)
	private Object selectUserFunds_new(ParamsVo<SvmCaptionBalance> vo) throws Exception {
		vo.setMethodKey("UserFunds_new");
		MethodParam param = setMethodParams(vo, 2);
		this.buildSql(param);
		List list = svmCaptionBalanceDao.select(param);
		//		log.info("fund资金余额查询：结果 -> "+list.size());
		return list;
	}
	
	//银行对账-查询账户余额 m1+m2
	public Object selectUserFundBala(ParamsVo<SvmCaptionBalance> vo) throws Exception {
		vo.setMethodKey("UserFundBala");
		MethodParam param = setMethodParams(vo, 2);
		this.buildSql(param);
		List list = svmCaptionBalanceDao.select(param);
		//		log.info("fund资金余额查询[银行对账-查询账户余额 m1+m2]：结果 -> "+list.size());
		return list;
	}

	// 拼接动态SQL
	private void buildSql(MethodParam param) {
		List<FundsTemplate> list = Constants.getAllCaptionTemplate();
		StringBuffer captionIdNames = new StringBuffer(); // 返回的资金科目名称
		StringBuffer captionIdValues = new StringBuffer(); // 返回的资金对应科目的金额
		StringBuffer captionBalanceValues = new StringBuffer(); // 返回日终表中的金额
		StringBuffer captionVoucherDetailValues = new StringBuffer(); // 返回明细表中的金额
		String captionId = "as captionId";
		for (int i = 0; i < list.size(); i++) {
			FundsTemplate ft = list.get(i);
			captionIdNames.append(",'").append(ft.getCaption()).append("' ").append(captionId)
					.append(getStorageNum(ft.getCaption()));
			captionIdValues.append(",sum(t.").append(ft.getCaption()).append(") ").append(ft.getCaption());
			captionBalanceValues.append(",NVL(max(case when t.caption_id='").append(ft.getCaption())
					.append("' then t.balance end),0) ").append(ft.getCaption());
			captionVoucherDetailValues.append(",NVL(sum(case when t.caption_id='").append(ft.getCaption())
					.append("' and t.direction='in' then t.amt when t.caption_id='").append(ft.getCaption())
					.append("' and t.direction='out' then t.amt*-1 end),0) ").append(ft.getCaption());
		}
		param.setParams("captionIdNames", captionIdNames.toString());
		param.setParams("captionIdValues", captionIdValues.toString());
		param.setParams("captionBalanceValues", captionBalanceValues.toString());
		param.setParams("captionVoucherDetailValues", captionVoucherDetailValues.toString());
	}

	public Page getPageInfo(ParamsVo<SvmCaptionBalance> vo) {
		MethodParam param = setMethodParams(vo, 2);
		int pageSize = Integer.valueOf(Validator.getDefaultStr(String.valueOf(vo.getParams("pageSize")), "10"));
		int pageIndex = Integer.valueOf(Validator.getDefaultStr(String.valueOf(vo.getParams("pageIndex")), "1"));
		param.setPageIndex(pageIndex);
		param.setPageSize(pageSize);
		Page page = svmCaptionBalanceDao.pageSelect(param);
		return page;
	}

	public List<SvmCaptionBalance> getList(ParamsVo<SvmCaptionBalance> vo) {
		MethodParam param = setMethodParams(vo, 2);
		return svmCaptionBalanceDao.select(param);
	}

	public long countInfo(ParamsVo<SvmCaptionBalance> vo) {
		MethodParam param = setMethodParams(vo, 1);
		return svmCaptionBalanceDao.count(param);
	}

	private MethodParam setMethodParams(ParamsVo<SvmCaptionBalance> vo, int type) {
		String methodKey = Validator.getDefaultStr(vo.getMethodKey(), "ByProperty");
		StringBuffer sb = new StringBuffer(svmCaptionBalanceDao.getSql(type));
		MethodParam param = new MethodParam(methodKey, "", "", entityName);
		//SvmCaptionBalance sptsCaptionBalance = vo.getObj();

		if (Validator.isNotNull(String.valueOf(vo.getParams("userId")))) {
			sb.append(" and userId = :userId");
			param.setParams("userId", String.valueOf(vo.getParams("userId")));
		}

		if (Validator.isNotNull(String.valueOf(vo.getParams("captionId")))) {
			sb.append(" and captionId = :captionId");
			param.setParams("captionId", String.valueOf(vo.getParams("captionId")));
		}

		if (Validator.isNotNull(String.valueOf(vo.getParams("dateTrade")))) {
			sb.append(" and dateTrade = :dateTrade");
			param.setParams("dateTrade", String.valueOf(vo.getParams("dateTrade")));
		}

		if (Validator.isNotNull(String.valueOf(vo.getParams("dateTradeYesterday")))) {
			sb.append(" and dateTrade = :dateTrade");
			param.setParams("dateTradeYesterday", String.valueOf(vo.getParams("dateTradeYesterday")));
		}

		param.setSqlStr(sb.toString());
		return param;
	}

	public static int isNumeric(String str) {
		for (int i = 0; i < str.length(); i++) {
			if (Character.isDigit(str.charAt(i))) {
				return i;
			}
		}
		return 0;
	}

	public static String getStorageNum(String storage) {
		int p = isNumeric(storage);
		return storage.substring(p);
	}

	public static void main(String[] args) {
		List<FundsTemplate> list = Constants.getAllCaptionTemplate();
		StringBuffer captionIdNames = new StringBuffer(); // 返回的资金科目名称
		StringBuffer captionIdValues = new StringBuffer(); // 返回的资金对应科目的金额
		StringBuffer captionBalanceValues = new StringBuffer(); // 返回日终表中的金额
		StringBuffer captionVoucherDetailValues = new StringBuffer(); // 返回明细表中的金额
		String captionId = "as captionId";
		for (int i = 0; i < list.size(); i++) {
			FundsTemplate ft = list.get(i);
			captionIdNames.append(",'").append(ft.getCaption()).append("' ").append(captionId)
					.append(getStorageNum(ft.getCaption()));
			captionIdValues.append(",sum(t.").append(ft.getCaption()).append(") ").append(ft.getCaption());
			captionBalanceValues.append(",NVL(max(case when sp.caption_id='").append(ft.getCaption())
					.append("' then sp.balance end),0) ").append(ft.getCaption()).append("\r\n");
			captionVoucherDetailValues.append(",NVL(sum(case when cd.caption_id='").append(ft.getCaption())
					.append("' and cd.direction='in' then cd.amt when cd.caption_id='").append(ft.getCaption())
					.append("' and cd.direction='out' then cd.amt*-1 end),0) ").append(ft.getCaption()).append("\r\n");
		}
		StringBuffer sb = new StringBuffer();
		sb.append("select t.user_id userId ");
		sb.append(captionIdNames).append("\r\n").append(captionIdValues);
		sb.append("  \r\n from \r\n (SELECT");
		sb.append(" \r\n sp.user_id \r\n");
		sb.append(captionBalanceValues);
		sb.append("from SPTS_CAPTION_BALANCE sp \r\n GROUP BY \r\n sp.user_id \r\n union \r\n SELECT \r\n cd.user_id");
		sb.append(captionVoucherDetailValues);
		sb.append(" from svm_voucher_detail cd \r\n GROUP BY cd.user_id");
		sb.append(") t \r\n group by t.user_id");
		System.out.println(sb.toString());
	}
}
